﻿Public Class Dubigeo

    Public Shared Function Seleccionar(ByVal descEmpresa As String) As List(Of Eubigeo)
        Dim ubigeos As List(Of Eubigeo) = Nothing

        Using cn As New SqlConnection(ConfigurationManager.ConnectionStrings(descEmpresa).ConnectionString)
            Using cmd As New SqlCommand("usp_ubigeo_sel", cn)
                cmd.CommandType = CommandType.StoredProcedure
                Try
                    cn.Open()
                    Using dr As SqlDataReader = cmd.ExecuteReader(CommandBehavior.SingleResult Or CommandBehavior.CloseConnection)
                        If dr.HasRows Then
                            ubigeos = New List(Of Eubigeo)
                            Dim ubigeo As Eubigeo = Nothing
                            Do While dr.Read
                                ubigeo = New Eubigeo( _
                                    dr.GetString(dr.GetOrdinal("idUbigeo")), _
                                    dr.GetString(dr.GetOrdinal("descUbigeo")))
                                ubigeos.Add(ubigeo)
                            Loop
                            dr.Close()
                        End If
                    End Using
                Catch ex As Exception
                    Throw
                End Try
            End Using
        End Using

        Return ubigeos
    End Function
    Public Shared Function SeleccionarTodoXDepartamento(ByVal descEmpresa As String) As List(Of Eubigeo)
        Dim ubigeos As List(Of Eubigeo) = Nothing

        Using cn As New SqlConnection(ConfigurationManager.ConnectionStrings(descEmpresa).ConnectionString)
            Using cmd As New SqlCommand("usp_departamento_sel", cn)
                cmd.CommandType = CommandType.StoredProcedure
                Try
                    cn.Open()
                    Using dr As SqlDataReader = cmd.ExecuteReader(CommandBehavior.SingleResult Or CommandBehavior.CloseConnection)
                        If dr.HasRows Then
                            ubigeos = New List(Of Eubigeo)
                            Dim ubigeo As Eubigeo = Nothing
                            Do While dr.Read
                                ubigeo = New Eubigeo( _
                                    dr.GetString(dr.GetOrdinal("idUbigeo")), _
                                    dr.GetString(dr.GetOrdinal("descripcion")))
                                ubigeos.Add(ubigeo)
                            Loop
                            dr.Close()
                        End If
                    End Using
                Catch ex As Exception
                    Throw
                End Try
            End Using
        End Using

        Return ubigeos
    End Function
    Public Shared Function SeleccionarTodoXProvincia(ByVal descEmpresa As String, ByVal idDepartamento As String) As List(Of Eubigeo)
        Dim ubigeos As List(Of Eubigeo) = Nothing

        Using cn As New SqlConnection(ConfigurationManager.ConnectionStrings(descEmpresa).ConnectionString)
            Using cmd As New SqlCommand("usp_provincia_sel", cn)
                cmd.CommandType = CommandType.StoredProcedure
                cmd.Parameters.Add("@idDepartamento", SqlDbType.Char, 2).Value = idDepartamento
                Try
                    cn.Open()
                    Using dr As SqlDataReader = cmd.ExecuteReader(CommandBehavior.SingleResult Or CommandBehavior.CloseConnection)
                        If dr.HasRows Then
                            ubigeos = New List(Of Eubigeo)
                            Dim ubigeo As Eubigeo = Nothing
                            Do While dr.Read
                                ubigeo = New Eubigeo( _
                                    dr.GetString(dr.GetOrdinal("idUbigeo")), _
                                    dr.GetString(dr.GetOrdinal("descripcion")))
                                ubigeos.Add(ubigeo)
                            Loop
                            dr.Close()
                        End If
                    End Using
                Catch ex As Exception
                    Throw
                End Try
            End Using
        End Using

        Return ubigeos
    End Function
    Public Shared Function SeleccionarTodoXDistrito(ByVal descEmpresa As String, ByVal idDepartamento As String, ByVal idProvincia As String) As List(Of Eubigeo)
        Dim ubigeos As List(Of Eubigeo) = Nothing

        Using cn As New SqlConnection(ConfigurationManager.ConnectionStrings(descEmpresa).ConnectionString)
            Using cmd As New SqlCommand("usp_distrito_sel", cn)
                cmd.CommandType = CommandType.StoredProcedure
                cmd.Parameters.Add("@idDepartamento", SqlDbType.Char, 2).Value = idDepartamento
                cmd.Parameters.Add("@idProvincia", SqlDbType.Char, 2).Value = idProvincia
                Try
                    cn.Open()
                    Using dr As SqlDataReader = cmd.ExecuteReader(CommandBehavior.SingleResult Or CommandBehavior.CloseConnection)
                        If dr.HasRows Then
                            ubigeos = New List(Of Eubigeo)
                            Dim ubigeo As Eubigeo = Nothing
                            Do While dr.Read
                                ubigeo = New Eubigeo( _
                                    dr.GetString(dr.GetOrdinal("idUbigeo")), _
                                    dr.GetString(dr.GetOrdinal("descripcion")))
                                ubigeos.Add(ubigeo)
                            Loop
                            dr.Close()
                        End If
                    End Using
                Catch ex As Exception
                    Throw
                End Try
            End Using
        End Using

        Return ubigeos
    End Function

    '******* RptGerenciales xDistrito  *******'
    Public Shared Function rptGerenciales_xDistrito(ByVal descEmpresa As String, ByVal rptGerencial As EproformaCabecera) As List(Of Eubigeo)
        Dim ubigeos As List(Of Eubigeo) = Nothing

        Using cn As New SqlConnection(ConfigurationManager.ConnectionStrings(descEmpresa).ConnectionString)
            Using cmd As New SqlCommand("usp_rptGerencial_xDistrito", cn)
                cmd.CommandType = CommandType.StoredProcedure
                cmd.Parameters.Add("@idProyecto", SqlDbType.Int).Value = rptGerencial.idProyecto
                cmd.Parameters.Add("@idEtapa", SqlDbType.Int).Value = rptGerencial.idEtapa
                cmd.Parameters.Add("@idUbigeo", SqlDbType.Char, 6).Value = rptGerencial.idUbigeo
                cmd.Parameters.Add("@FechaInicial", SqlDbType.Char, 10).Value = rptGerencial.fchInicio
                cmd.Parameters.Add("@FechaFinal", SqlDbType.Char, 10).Value = rptGerencial.fchFin
                Try
                    cn.Open()
                    Using dr As SqlDataReader = cmd.ExecuteReader(CommandBehavior.SingleResult Or CommandBehavior.CloseConnection)
                        If dr.HasRows Then
                            ubigeos = New List(Of Eubigeo)
                            Dim ubigeo As Eubigeo = Nothing
                            Do While dr.Read
                                ubigeo = New Eubigeo( _
                                    dr.GetString(dr.GetOrdinal("idUbigeo")), _
                                    dr.GetString(dr.GetOrdinal("descripcion")), _
                                    dr.GetInt32(dr.GetOrdinal("cantidad")), _
                                    dr.GetDecimal(dr.GetOrdinal("porcentaje")))
                                ubigeos.Add(ubigeo)
                            Loop
                            dr.Close()
                        End If
                    End Using
                Catch ex As Exception
                    Throw
                End Try
            End Using
        End Using

        Return ubigeos
    End Function

End Class
